﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using ERP_RORZE_SYSTEM;
using System.Globalization;

namespace ERP_RORZE_DLL.Business
{
    public partial class frm_Add_MasterPODetails : Form
    {
        private INISettings mySetting;
        private SqlConnection myConnection;

        private string supplier;
        private string transportid;
        private string transportname;
        private DataTable orderTable;

        public frm_Add_MasterPODetails()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.orderTable = new DataTable();
            this.supplier = null;
            this.transportid = null;
            this.transportname = null;

            this.poID.SelectedIndexChanged += poID_SelectedIndexChanged;
        }

        private void frm_Add_MasterPODetails_Load(object sender, EventArgs e)
        {
            LoadPOList();

            this.orderTable.Columns.Add("Check", typeof(bool));
            this.orderTable.Columns.Add("OrderNo", typeof(string));
            this.orderTable.Columns.Add("POID", typeof(string));
            this.orderTable.Columns.Add("SupplierID", typeof(string));
            this.orderTable.Columns.Add("SupplierName", typeof(string));
            this.orderTable.Columns.Add("PartNo", typeof(string));
            this.orderTable.Columns.Add("Quantity", typeof(float));
            this.orderTable.Columns.Add("POQuantity", typeof(float));
            this.orderTable.Columns.Add("UnitID", typeof(string));
            this.orderTable.Columns.Add("Price", typeof(float));
            this.orderTable.Columns.Add("CurrencyID", typeof(string));
            this.orderTable.Columns.Add("DeliveryDate", typeof(DateTime));
            this.orderTable.Columns.Add("TransportID", typeof(string));
            this.orderTable.Columns.Add("TransportName", typeof(string));
            this.orderTable.Columns.Add("DeliveryTermID", typeof(string));
            this.orderTable.Columns.Add("DeliveryTermName", typeof(string));
            this.orderTable.Columns.Add("Note", typeof(string));
            this.orderTable.Columns.Add("Purpose", typeof(string));                      
        }

        

        /////////////////////////////////////////
        /// Load List of Master PO
        ////////////////////////////////////////
        private void LoadPOList()
        {
            this.poID.Properties.Items.Clear();
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT ID FROM [241_MasterPO] WHERE Cancelled=0 ORDER BY ID";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["ID"].ToString();
                    item.Value = dataReader["ID"].ToString();
                    this.poID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Master PO");
            }
        }

        ////////////////////////////////////////
        /// Get Order for Selected PO
        ////////////////////////////////////////
        void poID_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.poID.SelectedIndex >= 0)
            {
                this.supplier = null;
                this.orderTable.Rows.Clear();
                ComboboxItem item = (ComboboxItem)this.poID.SelectedItem;
                string[] poinfo = getSupplierID(item.Value.ToString());
                this.supplier = poinfo[0];
                this.transportid = poinfo[1];
                this.transportname = poinfo[2];

                if (!string.IsNullOrEmpty(supplier))
                {
                    getLocalOrder();
                    getAS400Order();
                }

                this.gridImportOrder.DataSource = null;
                this.gridImportOrder.DataSource = this.orderTable;
                ImportOrder.Columns[0].Caption = "";
                ImportOrder.Columns[1].Caption = "Order No";
                ImportOrder.Columns[2].Caption = "PO ID";
                ImportOrder.Columns[3].Caption = "Supplier ID";
                ImportOrder.Columns[4].Caption = "Supplier Name";
                ImportOrder.Columns[5].Caption = "Part No";
                ImportOrder.Columns[6].Caption = "Quantity";
                ImportOrder.Columns[7].Caption = "PO Quantity";
                ImportOrder.Columns[8].Caption = "Unit ID";
                ImportOrder.Columns[9].Caption = "Price";
                ImportOrder.Columns[10].Caption = "Currency";
                ImportOrder.Columns[11].Caption = "Delivery Date";
                ImportOrder.Columns[12].Caption = "Transport ID";
                ImportOrder.Columns[13].Caption = "Transport Name";
                ImportOrder.Columns[14].Caption = "Delivery Term ID";
                ImportOrder.Columns[15].Caption = "Delivery Term Name";
                ImportOrder.Columns[16].Caption = "Note";
                ImportOrder.Columns[17].Caption = "Purpose";

                ImportOrder.Columns[12].Visible = false;
                ImportOrder.Columns[14].Visible = false;

                ImportOrder.Columns[0].OptionsColumn.AllowEdit = true;
                ImportOrder.Columns[1].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[2].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[3].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[4].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[5].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[6].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[7].OptionsColumn.AllowEdit = true;
                ImportOrder.Columns[8].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[9].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[10].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[11].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[12].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[13].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[14].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[15].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[16].OptionsColumn.AllowEdit = false;
                ImportOrder.Columns[17].OptionsColumn.AllowEdit = false;

                ImportOrder.BestFitColumns();

                ImportOrder.CellValueChanged += ImportOrder_CellValueChanged;
            }
        }

        void ImportOrder_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
        {
            ImportOrder.PostEditor();
            ImportOrder.UpdateCurrentRow();
        }

        /////////////////////////////////////////
        // Get Supplier ID of Master PO
        ////////////////////////////////////////
        private string[] getSupplierID(string poid)
        {
            string[] ret = new string[3];
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT t1.SupplierID, t1.TransportID, t2.TransportName"
                    + " FROM [241_MasterPO] AS t1 LEFT OUTER JOIN [222_Transport] AS t2 ON t1.TransportID=t2.TransportID"
                    + " WHERE ID='" + poid + "'";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    ret[0] = dataReader["SupplierID"].ToString();
                    ret[1] = dataReader["TransportID"].ToString();
                    ret[2] = dataReader["TransportName"].ToString();
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Supplier ID for selected PO");
            }

            return ret;
        }

        ////////////////////////////////////////
        /// Get Order list from software
        ////////////////////////////////////////
        private void getLocalOrder()
        {
            if (!string.IsNullOrEmpty(this.supplier))
            {                
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT t1.OrderNo, t1.POID, t2.SupplierID, t3.SupplierName,"
                        + " t1.PartNo, t1.Quantity - ISNULL(t6.Quantity, 0) AS Quantity, t1.UnitID, t1.UnitPrice,"
                        + " t1.CurrencyID, t1.DeliveryDate, t1.TransportID, t4.TransportName, t1.DeliveryTermID,"
                        + " t5.DeliveryTermName, t1.Note, t1.Purpose"
                        + " FROM [241_PODetails] AS t1"
                        + " LEFT JOIN [241_PO] AS t2 ON t1.POID=t2.ID"
                        + " LEFT JOIN [222_Supplier] AS t3 ON t2.SupplierID=t3.SupplierID"
                        + " LEFT JOIN [222_Transport] AS t4 ON t1.TransportID=t4.TransportID"
                        + " LEFT JOIN [222_DeliveryTerm] AS t5 ON t1.DeliveryTermID=t5.DeliveryTermID"
                        + " LEFT JOIN [VMasterPODetails] AS t6 ON t1.OrderNo=t6.OrderNo"
                        + " WHERE t1.Cancelled=0 AND t1.Change=0 AND t2.SupplierID='" + this.supplier + "'"
                        + " AND DATEDIFF(day,t1.DeliveryDate,GETDATE()) < 365";
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    while (dataReader.Read())
                    {
                        float quantity = 0;
                        float.TryParse(dataReader["Quantity"].ToString(), out quantity);
                        if (quantity > 0)
                        {
                            DataRow new_row = this.orderTable.NewRow();
                            new_row["Check"] = false;
                            new_row["OrderNo"] = dataReader["OrderNo"].ToString();
                            new_row["POID"] = dataReader["POID"].ToString();
                            new_row["SupplierID"] = dataReader["SupplierID"].ToString();
                            new_row["SupplierName"] = dataReader["SupplierName"].ToString();
                            new_row["PartNo"] = dataReader["PartNo"].ToString();
                            new_row["Quantity"] = quantity;
                            new_row["POQuantity"] = quantity;
                            new_row["UnitID"] = dataReader["UnitID"].ToString();
                            new_row["Price"] = dataReader["UnitPrice"].ToString();
                            new_row["CurrencyID"] = dataReader["CurrencyID"].ToString();
                            new_row["DeliveryDate"] = DateTime.Parse(dataReader["DeliveryDate"].ToString());
                            new_row["TransportID"] = this.transportid;
                            new_row["TransportName"] = this.transportname;
                            /*
                            if (string.IsNullOrEmpty(dataReader["TransportID"].ToString()))
                            {
                                new_row["TransportID"] = this.transportid;
                                new_row["TransportName"] = this.transportname;
                            }
                            else
                            {
                                new_row["TransportID"] = dataReader["TransportID"].ToString();
                                new_row["TransportName"] = dataReader["TransportName"].ToString();
                            } */                           
                            new_row["DeliveryTermID"] = dataReader["DeliveryTermID"].ToString();
                            new_row["DeliveryTermName"] = dataReader["DeliveryTermName"].ToString();
                            new_row["Note"] = dataReader["Note"].ToString();
                            new_row["Purpose"] = dataReader["Purpose"].ToString();
                            this.orderTable.Rows.Add(new_row);
                        }
                        
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    //MessageBox.Show("Cannot get Order for selected PO");
                }
            }
        }

        ////////////////////////////////////////
        /// Get Order list from AS400
        ////////////////////////////////////////
        private void getAS400Order()
        {
            if (!string.IsNullOrEmpty(this.supplier))
            {
                DataTable as400OrderTable = new DataTable();
                as400OrderTable.Columns.Add("Check", typeof(bool));
                as400OrderTable.Columns.Add("OrderNo", typeof(string));
                as400OrderTable.Columns.Add("POID", typeof(string));
                as400OrderTable.Columns.Add("SupplierID", typeof(string));
                as400OrderTable.Columns.Add("SupplierName", typeof(string));
                as400OrderTable.Columns.Add("PartNo", typeof(string));
                as400OrderTable.Columns.Add("Quantity", typeof(float));
                as400OrderTable.Columns.Add("POQuantity", typeof(float));
                as400OrderTable.Columns.Add("UnitID", typeof(string));
                as400OrderTable.Columns.Add("Price", typeof(float));
                as400OrderTable.Columns.Add("CurrencyID", typeof(string));
                as400OrderTable.Columns.Add("DeliveryDate", typeof(DateTime));
                as400OrderTable.Columns.Add("TransportID", typeof(string));
                as400OrderTable.Columns.Add("TransportName", typeof(string));
                as400OrderTable.Columns.Add("DeliveryTermID", typeof(string));
                as400OrderTable.Columns.Add("DeliveryTermName", typeof(string));
                as400OrderTable.Columns.Add("Note", typeof(string));
                as400OrderTable.Columns.Add("Purpose", typeof(string));
                
                DateTime today = DateTime.Now;
                TimeSpan duration = new TimeSpan(-30,0,0,0);
                DateTime getday = today.Add(duration);
                string order_day = getday.ToString("yyyyMMdd");                
                
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();

                    // Get Order list
                    cmd_sql.CommandText = "SELECT t1.PO, t1.PARTNO, t1.ORDERNO,"
                        + " ROUND((t1.TOTALQUANTITY - ISNULL(t3.Quantity, 0)), 1) AS TOTALQUANTITY,"
                        + " t1.UNITID, t1.PRICE,"
                        + " CASE WHEN t1.CURRENCY = '\' THEN 'JPY' WHEN t1.CURRENCY = 'D' THEN 'VND'"
                        + " WHEN t1.CURRENCY = '$' THEN 'USD' WHEN t1.CURRENCY = 'B' THEN 'THB' ELSE t1.CURRENCY END AS CURRENCY,"
                        + " t1.DELIVERYDATE, t1.SUPPLIERID, t2.SupplierName"
                        + " FROM OPENQUERY(AS400_MPFLIB, 'SELECT SUBSTRING(t1.ORFILL, 50, 7) AS PO, t1.ORHNBN AS PARTNO,"
                        + " t1.ORODNO AS ORDERNO,"
                        + " t1.ORHTSU/(CASE WHEN t3.ITKANZ IS NOT NULL AND t3.ITKANZ <>'''' THEN t3.ITKANZ ELSE 1 END) AS TOTALQUANTITY,"
                        + " t3.ITHTAN AS UNITID, t1.ORTANK AS PRICE, SUBSTRING(t6.GAFILL, 43, 1) AS CURRENCY,"
                        + " t5.O8NOK3 AS DELIVERYDATE, t1.ORSICD AS SUPPLIERID"
                        + " FROM MPOR00 AS t1 LEFT JOIN"                        
                        + " MPIT00 AS t3 ON t1.ORHNBN=t3.ITHNBN LEFT JOIN"                        
                        + " MPOR80 AS t5 ON t1.ORODNO=t5.O8ODNO LEFT JOIN"
                        + " MPGA00 AS t6 ON t1.ORSICD=t6.GASICD"
                        + " WHERE t1.ORHNBN NOT LIKE ''CSZ0-%'' AND t1.ORSICD = ''" + this.supplier + "''"
                        + " AND t5.O8NOK3 <> ''''"
                        + "') AS t1 LEFT JOIN"
                        + " [222_Supplier] AS t2 ON t1.SUPPLIERID COLLATE Vietnamese_CI_AS = t2.SupplierID LEFT JOIN"
                        + " [VMasterPODetails] AS t3 ON t1.ORDERNO COLLATE Vietnamese_CI_AS = t3.OrderNo";
                    cmd_sql.CommandTimeout = 30000;
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    while (dataReader.Read())
                    {
                        float quantity = 0;
                        float.TryParse(dataReader["TOTALQUANTITY"].ToString().Trim(), out quantity);
                        if (quantity > 0)
                        {
                            DataRow new_row = as400OrderTable.NewRow();
                            new_row["Check"] = false;
                            new_row["OrderNo"] = dataReader["ORDERNO"].ToString().Trim();
                            new_row["POID"] = dataReader["PO"].ToString().Trim();
                            new_row["SupplierID"] = dataReader["SUPPLIERID"].ToString().Trim();
                            new_row["SupplierName"] = dataReader["SupplierName"].ToString().Trim();
                            new_row["PartNo"] = dataReader["PARTNO"].ToString().Trim();
                            new_row["Quantity"] = quantity;
                            new_row["POQuantity"] = quantity;
                            string unit = null;
                            unit = dataReader["UNITID"].ToString().Trim();
                            if (unit.Length > 0)
                            {
                                char x = unit[0];
                                if (((int)x) == 65402)
                                {
                                    new_row["UnitID"] = "PC";
                                }
                                else
                                {
                                    new_row["UnitID"] = unit;
                                }
                            }                            
                            float price = 0;
                            float.TryParse(dataReader["PRICE"].ToString().Trim(), out price);
                            new_row["Price"] = price;                            
                            string currency = dataReader["CURRENCY"].ToString().Trim();
                            switch (currency)
                            {
                                case "\\":
                                    currency = "JPY";
                                    break;
                            } 
                            new_row["CurrencyID"] = currency;
                            DateTime deliver = new DateTime();
                            if (DateTime.TryParseExact(dataReader["DELIVERYDATE"].ToString().Trim(), "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None, out deliver))
                            {
                                new_row["DeliveryDate"] = deliver.Date;
                            }
                            new_row["TransportID"] = this.transportid;
                            new_row["TransportName"] = this.transportname;
                            as400OrderTable.Rows.Add(new_row);
                        }
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();

                    
                    // Get Receive list
                    cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT t1.PARTNO, t1.ORDERNO,"
                        + " ROUND((SUM(t1.RECEIVEQUANTITY) - ISNULL(t2.Quantity, 0)), 1) AS RECEIVEQUANTITY, t1.SUPPLIERID,"
                        + " t3.SupplierName, ROUND((SUM(t1.TOTALCOST)/SUM(t1.RECEIVEQUANTITY)), 2) AS PRICE, t1.CURRENCYID,"
                        + " t1.UNITID, t1.DELIVERYDATE"
                        + " FROM OPENQUERY(AS400_MPFLIB, 'SELECT"
                        + " CASE WHEN (t1.HSHNBN LIKE ''CSZ0-%'' AND t1.HSTRCD=''RM'')"
                        + " THEN SUBSTRING(t1.HSHNBN, 6, LENGTH(t1.HSHNBN)-5) ELSE t1.HSHNBN END AS PARTNO, t3.I3IQNO AS IQID,"
                        + " t1.HSODNO AS ORDERNO,ROUND( (t1.HSTRSU/(CASE WHEN t2.ITKANZ IS NOT NULL AND t2.ITKANZ <>''''"
                        + " THEN t2.ITKANZ ELSE 1 END)), 2) AS RECEIVEQUANTITY, t1.HSSICD AS SUPPLIERID, t1.HSKKAI AS TOTALCOST,"
                        + " ''USD'' AS CURRENCYID, t2.ITHTAN AS UNITID, t2.ITTNI1 AS IQUNIT, t1.HSTDTE AS DELIVERYDATE"
                        + " FROM MPHS00 AS t1"
                        + " LEFT JOIN MPIT00 AS t2 ON t1.HSHNBN=t2.ITHNBN"
                        + " LEFT JOIN MPIT30 AS t3 ON t1.HSHNBN=t3.I3HNBN"
                        + " WHERE t1.HSTDTE > ''" + order_day + "'' AND (t1.HSTRCD=''RP'' OR t1.HSTRCD=''RM'')"
                        + " AND t1.HSSICD=''" + this.supplier + "''') AS t1"
                        + " LEFT JOIN [VMasterPODetails] AS t2 ON t1.ORDERNO COLLATE Vietnamese_CI_AS=t2.OrderNo"
                        + " LEFT JOIN [222_Supplier] AS t3 ON t1.SUPPLIERID COLLATE Vietnamese_CI_AS=t3.SupplierID"
                        + " GROUP BY t1.PARTNO, t1.ORDERNO, t1.SUPPLIERID, t1.CURRENCYID, t1.UNITID,"
                        + " t1.DELIVERYDATE, t3.SupplierName, t2.Quantity";
                    cmd_sql.CommandTimeout = 30000;
                    dataReader = cmd_sql.ExecuteReader();
                    while (dataReader.Read())
                    {
                        DataRow[] exist_rows = as400OrderTable.Select("OrderNo='" + dataReader["ORDERNO"].ToString().Trim() + "'");
                        if (exist_rows.Length <= 0)
                        {
                            float qty = 0;
                            float.TryParse(dataReader["RECEIVEQUANTITY"].ToString(), out qty);
                            if (qty > 0)
                            {
                                DataRow rec_row = as400OrderTable.NewRow();
                                rec_row["Check"] = false;
                                rec_row["OrderNo"] = dataReader["ORDERNO"].ToString().Trim();
                                rec_row["POID"] = "0000000";
                                rec_row["SupplierID"] = dataReader["SUPPLIERID"].ToString().Trim();
                                rec_row["SupplierName"] = dataReader["SupplierName"].ToString().Trim();
                                rec_row["PartNo"] = dataReader["PARTNO"].ToString().Trim();
                                rec_row["Quantity"] = qty;
                                rec_row["POQuantity"] = qty;
                                string unit = null;
                                unit = dataReader["UNITID"].ToString().Trim();
                                if (unit.Length > 0)
                                {
                                    char x = unit[0];
                                    if (((int)x) == 65402)
                                    {
                                        rec_row["UnitID"] = "PC";
                                    }
                                    else
                                    {
                                        rec_row["UnitID"] = unit;
                                    }
                                }                                
                                float price = 0;
                                float.TryParse(dataReader["PRICE"].ToString().Trim(), out price);
                                rec_row["Price"] = price;
                                rec_row["CurrencyID"] = dataReader["CURRENCYID"].ToString().Trim();
                                DateTime deliver = new DateTime();
                                if (DateTime.TryParseExact(dataReader["DELIVERYDATE"].ToString().Trim(), "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None, out deliver))
                                {
                                    rec_row["DeliveryDate"] = deliver.Date;
                                }
                                rec_row["TransportID"] = this.transportid;
                                rec_row["TransportName"] = this.transportname;
                                as400OrderTable.Rows.Add(rec_row);
                            }
                        }
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();                    
                    myConnection.Close();

                    
                    foreach (DataRow densan_row in as400OrderTable.Rows)
                    {
                        this.orderTable.ImportRow(densan_row);
                    }
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    //MessageBox.Show("Cannot get Order for selected PO");
                } 
            }
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void buttonAccept_Click(object sender, EventArgs e)
        {
            ComboboxItem item = (ComboboxItem)this.poID.SelectedItem;
            string select_poid = item.Value.ToString();
            DataRow[] select_rows = this.orderTable.Select("Check=1");
            if (select_rows.Length > 0)
            {
                string check = CheckValidQuantity();
                if (string.IsNullOrEmpty(check))
                {
                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql;
                        foreach (DataRow select_row in select_rows)
                        {
                            cmd_sql = myConnection.CreateCommand();
                            cmd_sql.CommandText = "INSERT INTO [241_MasterPODetails] (POID, OrderNo, PartNo,"
                                + " Quantity, UnitID, UnitPrice, CurrencyID, DeliveryDate, TransportID, DeliveryTermID,"
                                + " Purpose, Note) VALUES ('" + select_poid + "',"
                                + " '" + select_row["OrderNo"].ToString() + "', N'" + select_row["PartNo"].ToString() + "',"
                                + " " + float.Parse(select_row["POQuantity"].ToString()) + ","
                                + " '" + select_row["UnitID"].ToString() + "',"
                                + " " + float.Parse(select_row["Price"].ToString()) + ","
                                + " '" + select_row["CurrencyID"].ToString() + "', @deliverydate,"
                                + " '" + select_row["TransportID"].ToString() + "',"
                                + " '" + select_row["DeliveryTermID"].ToString() + "',"
                                + " '" + select_row["Purpose"].ToString() + "',"
                                + " '" + select_row["Note"].ToString() + "')";
                            if (string.IsNullOrEmpty(select_row["DeliveryDate"].ToString()))
                            {
                                cmd_sql.Parameters.Add("@deliverydate", SqlDbType.DateTime).Value = DBNull.Value;
                            }
                            else
                            {
                                cmd_sql.Parameters.Add("@deliverydate", SqlDbType.DateTime).Value = DateTime.Parse(select_row["DeliveryDate"].ToString()).Date;
                            }
                            cmd_sql.ExecuteNonQuery();
                            cmd_sql.Dispose();

                            select_row.BeginEdit();
                            select_row["Quantity"] = float.Parse(select_row["Quantity"].ToString()) - float.Parse(select_row["POQuantity"].ToString());
                            select_row.EndEdit();
                        }
                        myConnection.Close();
                        
                        MessageBox.Show("Update successfully!");
                    }
                    catch //(Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        //MessageBox.Show(ex.Message);
                        MessageBox.Show("Cannot add selected order to Master PO. Please try again");
                    }
                }
                else
                {
                    MessageBox.Show(check);
                }
            }
        }
        
        ///////////////////////////////////////
        /// Check valid PO quantity
        ///////////////////////////////////////
        private string CheckValidQuantity()
        {
            string ret = null;            
            DataRow[] select_rows = this.orderTable.Select("Check=1");
            if (select_rows.Length > 0)
            {
                foreach (DataRow select_row in select_rows)
                {
                    float po_quantity = 0;
                    float total_quantity = 0;
                    float.TryParse(select_row["POQuantity"].ToString(), out po_quantity);
                    float.TryParse(select_row["Quantity"].ToString(), out total_quantity);

                    if (po_quantity <= 0)
                    {
                        ret = "There are some order has invalid PO quantity. Please check";
                    }
                    else
                    {
                        if (po_quantity > total_quantity)
                        {
                            ret = "There are some order has PO quantity greater than total quantity. Please check";
                        }
                    }
                }
            }
            else
            {
                ret = "There is no order selected.";
            }

            return ret;
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadPOList();
        }        
    }
}
